<?php
/**
 * 充值提现
 * @author Administrator
 *
 */
namespace app\admin\controller;

use app\admin\controller\Base;
use app\common\model\SystemSet as SystemSetModel;
use app\common\logic\StateChange as StateChangeLogic;
use app\common\model\StateChange as StateChangeModel;
use think\Db;
use app\index\model\ChongzhiList;
use app\index\model\Username;
use app\common\model\HuiYuanSupervise;

class Recharge extends Base
{
	protected $limit = 20;
	protected $system;
	protected $change_logic;
	protected $state;
	public function _initialize()
	{
		parent::_initialize();
		$this->system = new SystemSetModel();
		$this->change_logic = new StateChangeLogic();
		$this->state = new StateChangeModel();
		$this->member_model= new HuiYuanSupervise();
	}
	/**
	 * 未审核充值列表
	 * @return Ambigous <\think\mixed, string>
	 */
	public function cz_list_no(){

		$limit=input('limit',20);
		$come = input('wfrom',0);
		$timing = input('timing',0);
		$this->assign('timing',$timing);
		$this->assign('limit',$limit);
		$where = array();
		$stua=input('stua');
		if($stua==3){$where['a.zt']=0;$where['a.czclass']='1';}
		if($stua==0){$where['a.zt']=0;$where['a.czclass']='2';}
		if($stua==1){$where['a.czadmin']=['<>',''];$where['a.zt']='1';}
		if($stua==2){$where['a.zt']='-1';$where['a.czadmin']=['<>',''];}
		if($stua==9){$where['a.zt']='9';}
		$a='';
	   $star_time="";
	   $end_time="";
		if(input('server_user')){$a=input('server_user');}
		$a=input('server_user')?input('server_user'):input('username');
		if(input('server_ddbh')){$where['a.ddbh']=input('server_ddbh');}
		if(input('start') || input('end')){
		    $star_time = input('start') ? input('start')." 00:00:00" : date('Y-m-d',time())." 00:00:00";
		    $end_time = input('end') ? input('end')." 23:59:59" : date('Y-m-d',time())." 23:59:59";
		    $where['a.cztime']=['between',strtotime($star_time).','.strtotime($end_time)];
		}
		if( !empty($come) ){
			$come_attr = explode('_',$come);
			if( 2 == count($come_attr) ){
				if( 1==$come_attr[0] ){
				//第三方
					$pay_name = Db::name('pay_xianshang')->where(['id'=>$come_attr[1]])->value('label');
					$where['binary a.ddbh'] = ['like',$pay_name.'%'];
					$where['a.czclass'] = 1;
				}elseif( 2==$come_attr[0] ){
				//第四方
					$pay_name = Db::name('code_account')->where(['id'=>$come_attr[1]])->value('label');
					$where['binary a.ddbh'] = ['like',$pay_name.'%'];
					$where['a.czclass'] = 1;
				}elseif( 3==$come_attr[0] ){
				//线下
					$pay_name = Db::name('pay_xxlist')->where(['id'=>$come_attr[1]])->value('label');
					$where['binary a.ddbh'] = ['like',$pay_name.'%'];
					$where['a.czclass'] = 2;
				}
			}
		}
		$order = 'id desc';
		$query = Db::name('chongzhi_list')
				->alias('a')
				->join('__USERNAME__ b','b.username=a.username')
				->join('__US_CLASS__ c','c.id=b.us_class')
				->field('a.id,a.ddbh,a.zt,a.je,a.username,a.czclass,a.xxbank,a.cztime,a.pay_time,a.openren,a.czadmin,a.beizhu,a.czip,a.bankekh,a.liuyan,c.class_name,a.shtime,a.beizhu')
				->where($where)->where('a.username','like','%'.$a.'%')->order($order)->paginate($limit,false,['query'=>request()->param()]);
		$money=0;
		foreach($query as $v){
			if($v['zt']==1 && $v['czadmin']!=''){
				$money += $v['je'];
			}
		}
		$zhmoney=Db::name('chongzhi_list')->where('czadmin','<>','')->where('zt',1)->value('SUM(je)');
		$this->assign('username',$a);
		$this->assign('time1',input('start'));
		$this->assign('time2',input('end'));
		$this->assign('stua',input('stua'));
		$this->assign('zhmoney',$zhmoney);
		$this->assign('stau',input('stua')?input('stua'):'');
		$this->assign('money',$money);
		$this->assign('server_user',input('server_user'));
		$this->assign('query',$query);

		$pay_three = Db::name('pay_xianshang')->field('id,pay_name,1 as type')->where(['is_code'=>0])->select();
		$pay_four = Db::name('code_account')->alias('a')->join('__PAY_XIANSHANG__ b','a.type=b.label','LEFT')->field("a.id,a.name,b.pay_name,2 as type")->where(['b.is_code'=>1])->select();
		$pay_four = array_map( function($v){
			$v['pay_name'] = $v['name'].'_'.$v['pay_name'];
			unset($v['name']);
			return $v;
		},$pay_four );
		$pay_four = [];

		$pay_off = Db::name('pay_xxlist')->field('id,xx_name,xx_weixin,xx_zhifubao,xx_wangyin,xx_qq, 3 as type')->select();
		$pay_off = array_map( function($v){
			$v['pay_name'] = $v['xx_name'].'_';
			if( 1==$v['xx_weixin'] ){
				$v['pay_name'] .= '微信扫码';
			}elseif( 1==$v['xx_zhifubao'] ){
				$v['pay_name'] .= '支付宝扫码';
			}elseif( 1==$v['xx_wangyin'] ){
				$v['pay_name'] .= '网银转账';
			}elseif( 1==$v['xx_qq'] ){
				$v['pay_name'] .= 'QQ扫码';
			}else{
				$v['pay_name'] .= '未知';
			}
			unset($v['xx_name'],$v['xx_weixin'],$v['xx_zhifubao'],$v['xx_wangyin'],$v['xx_qq']);
			return $v;
		},$pay_off );
		$pay_list = array_merge($pay_three,$pay_four,$pay_off);
		if( 0==intval($stua) ){
			$pay_list = $pay_off;
		}
		$this->assign('pay_list',$pay_list);
		$this->assign('wfrom',$come);

		return $this->fetch();
	}
	public function userDetail(){
		$uid = input('uid');
		$txid = input('tx');
		$tx = Db::name('tixian_list')->where('id',$txid)->find();
		$query = Db::name('username')->alias('a')->join('__BANK__ b','b.id=a.bankname')->field('a.username,a.je,a.bank_zhihang,a.bankren,a.bankno,b.bankname as bank,a.dml')->where('a.username',$uid)->find();
		$this->assign('tx',$tx['txje']);
		$this->assign('query',$query);
		return $this->fetch('user_detail');
	}
	public function ManualReview(){
		Db::startTrans();
		$uid = input('czid');
		
		$order_info = Db::name('chongzhi_list')->field('username,hy_msje,hy_zsje,je,hy_msje,ddbh,method_money')->where(array('id'=>$uid,'zt'=>9))->find();
		if(empty($order_info)){
		    alert_back('该失效订单不存在或已经审核');
		}
		$user_info = Db::name('username')->field('username,us_class,czzje,dml,je')->where('username',$order_info['username'])->find();
		$dml_set = Db::name('xt_dmsz')->field('xscz_dmbs')->where('id',1)->find();
		$xtcz = Db::name('xt_chongzhi')->field('dmopen')->where('id',1)->find();
		
		if($xtcz['dmopen'] == 1) {
			$dmls = ($order_info['hy_msje'] + $order_info['hy_zsje'] + $order_info['je']) * $dml_set['xscz_dmbs'];
		}else{
			$dmls = $order_info['je'] * $dml_set['xscz_dmbs'];
		}
		$czhje = $order_info['hy_msje'] + $order_info['hy_zsje'] + $order_info['je'] + $user_info['je'];
            //充值总金额
        $czzje = $order_info['je'] + $user_info['czzje'];
            //用户当前打码量
        $dml = $user_info['dml'] + $dmls;
        try {
	        $username = new Username();
	        $username->upUserXsczInfo($czhje,$dml,$czzje,$order_info['username']);
	        
			$chongzhilist = new ChongzhiList();
			$chongzhilist->updateRechargeInfo($order_info['ddbh'], '', 1, $czhje,time());
			Db::name('chongzhi_list')->where('id',$uid)->update(array('czadmin'=>'自动回调失败，手动审核','pay_time'=>time()));
			$fav_money = $order_info['hy_msje']+$order_info['hy_zsje'];

			//满送金额，等级赠送金额，充值方案赠送金额
			$extcj = array(
					'username'=>$user_info['username'],
					'user_class'=>$user_info['us_class'],
					'fstime'=>time(),
					'czadmin' => '系统赠送',
			);
			$cjhje = floatval($order_info['je']) + floatval($user_info['je']) + floatval($order_info['hy_msje']);
			if( floatval($order_info['hy_msje']) > 0 ){
				$extcj['beizhu'] = '系统赠送，充值满送金额';
				$extcj['cjje'] = $order_info['hy_msje'];
				$extcj['cjhje'] = $cjhje;
				Db::name('caijin_list')->insert($extcj);
			}
			$cjhje += floatval($order_info['hy_zsje']);
			if( floatval($order_info['hy_zsje']) > 0 ){
				$extcj['beizhu'] = '系统赠送，充值等级赠送金额';
				$extcj['cjje'] = $order_info['hy_zsje'];
				$extcj['cjhje'] = $cjhje;
				Db::name('caijin_list')->insert($extcj);
			}
			$cjhje += floatval($order_info['method_money']);
			if( floatval($order_info['method_money']) > 0 ){
				$extcj['beizhu'] = '系统赠送，充值方案赠送金额';
				$extcj['cjje'] = $order_info['method_money'];
				$extcj['cjhje'] = $cjhje;
				Db::name('caijin_list')->insert($extcj);
			}

			$data = array('username' =>$order_info['username'],'test'=>"您账户充值金额：{$order_info['je']}，赠送金额：{$fav_money}。已充值到账，请注意查收！",'xx_time'=>time(),'ifdq'=>0);
			Db::name('xiaoxi')->data($data)->insert();
			Db::commit();
			$this->member_model->caozuorizhi_xieru('审核了无效订单列表中的账户:'.$order_info['username'].'充值'.$order_info['je']);
			return js_tishi('成功','cz_list_no?stua=1');
		} catch (\Exception $e) {
			Db::rollback();
			return js_tishi('审核失败', 'cz_list_no?stua=9');
		}
	}

	public function throughAudit()
	{
		$this->change_logic->throughAudit();
		return $this->redirect('/admin/recharge/cz_list_no');
	}

	public function oneKeyDelFail()
	{
		return $this->change_logic->oneKeyDelFail(input('stua'));
	}
	/**
	 * 未审核提现列表
	 * @return Ambigous <\think\mixed, string>
	 */
	public function tx_list(){

		$limit=input('get.limit')?input('get.limit'):20;
		$this->assign("limit",$limit);
		$username=input("post.us_sql")?input('post.us_sql'):input('get.username');
		$time1=input('post.sDate1')?input('post.sDate1'):input('get.time1');
		$time2=input('post.sDate2')?input('post.sDate2'):input('get.time2');
		if($time1 || $time2){
			$time1=$time1?$time1." 00:00:00":date('Y-m-d',time())." 00:00:00";
			$time2=$time2?$time2." 23:59:59":date('Y-m-d',time())." 23:59:59";
		}
		$query = $this->state->selectList($limit,$username,$time1,$time2);

		$money = 0;
		foreach ($query as &$v){
			$v['ddhm'] = 'T'.$v['txtime'].$v['uuid'];
			$cishu = Db::name('tixian_list')->where('username',$v['username'])->where('id','<=',$v['uuid'])->select();
			$v['cishu']=count($cishu);
			$money += $v['txje'];
		}
		$this->assign('username',$username);
		$this->assign('time1',$time1);
		$this->assign('time2',$time2);
		$this->assign('money',$money);
		$this->assign('query',$query);
		return $this->fetch();
	}
	public function toExamine()
	{
		$data=array('ifck'=>input('ifck'),'shadmin'=>$_COOKIE['user'],'shtime'=>time());

		$where=array('id'=>input('czid'),'ifck'=>['<',1],'zt'=>['<',1]);
		$query = $this->system->dFind('tixian_list',$where);

		if($query['id']!=''){
			if(Db::name('tixian_list')->where('id',input('czid'))->update($data)){
				$this->member_model->caozuorizhi_xieru("审核了账户:".$query['username']."取款".$query['txje']."的申请");
				return $this->system->alertWin('操作成功', "/admin/recharge/tx_list");

			}else{
				return $this->system->alertWin('操作失败', "/admin/recharge/tx_list");
			}
		}else{
			return $this->system->alertWin('操作错误,已有管理员在操作', "/admin/recharge/tx_list");
		}
	}
	
	public function successTurnover()
	{
		Db::startTrans();
		$data=array('zt'=>1,'czadmin'=>$_COOKIE['user'],'shtime'=>time());
		$where=array('id'=>input('czid'),'ifck'=>1,'zt'=>['<',1]);
		try{
			$query = Db::name('tixian_list')->where($where)->find();
			$d = Db::name('username')->where('username',$query['username'])->find();
			if($query['id']){
				$zje = $d['txzje']+$query['txje'];
				$data_tx=array('txzje'=>$zje);
//				$data['txhje'] = $d['je']; //提现后金额
				Db::name('username')->where('id',$d['id'])->update($data_tx);
				Db::name('tixian_list')->where('id',input('czid'))->update($data);
				Db::name('username')->where('id',$d['id'])->setInc('tx_num');
				Db::commit();
				$this->member_model->caozuorizhi_xieru("账户:".$query['username']."出款".$query['txje']);
				return $this->system->alertWin('操作成功', "/admin/recharge/tx_list");
			}else{
				Db::rollback();
				return $this->system->alertWin('操作错误，已有管理员在处理或者还未进行审核！', "/admin/recharge/tx_list");
			}
			
		}catch (\Exception $e){
			Db::rollback();
			return $this->system->alertWin('操作错误，已有管理员在处理或者还未进行审核！', "/admin/recharge/tx_list");
		}
		
	}
	public function tiXianReturn(){
		return $this->state->tiXianReturn(input('txid'));
	}
	public function tiXianReturnBeiZhu(){
		return $this->change_logic->tiXianReturnBeiZhu();
	}
	/**
	 * 已审核提现列表
	 */
	public function tx_list_ok()

	{
		$limit=input('get.limit')?input('get.limit'):20;
		$this->assign("limit",$limit);
		$username=input("post.us_sql")?input('post.us_sql'):input('get.username');
		$time1=input('post.sDate1')?input('post.sDate1'):input('get.time1');
		$time2=input('post.sDate2')?input('post.sDate2'):input('get.time2');
		$this->assign('time1',$time1);
		$this->assign('time2',$time2);
		if($time1 || $time2){
			$time1=$time1?$time1." 00:00:00":date('Y-m-d',time())." 00:00:00";
			$time2=$time2?$time2." 23:59:59":date('Y-m-d',time())." 23:59:59";
		}
		$query = $this->change_logic->txListOk($limit,$username,$time1,$time2);
		$money = 0;
		foreach ($query as &$v){
			$money += $v['txje'];
			$v['ddhm'] = 'T'.$v['txtime'].$v['uuid'];
		}
		$this->assign('username',$username);
		
		$this->assign('money',$money);
		$this->assign('query',$query);
		$this->assign('us_sql',input('get.us_sql'));
		return $this->fetch();
	}
	public function xiangqing(){
		$username = input('user');
		$user = Db::name('username')->where('username',$username)->find();
		$this->assign('user',$user);
		return $this->fetch();
	}
	/**
	 * 提现退回列表
	 */
	public function tx_list_no()
	{
		$limit=input('get.limit')?input('get.limit'):20;
		$this->assign("limit",$limit);
		$username=input("post.us_sql")?input('post.us_sql'):input('get.username');
		$time1=input('post.sDate1')?input('post.sDate1'):input('get.time1');
		$time2=input('post.sDate2')?input('post.sDate2'):input('get.time2');
		$this->assign('time1',$time1);
		$this->assign('time2',$time2);
		if($time1 || $time2){
			$time1=$time1?$time1." 00:00:00":date('Y-m-d',time())." 00:00:00";
			$time2=$time2?$time2." 23:59:59":date('Y-m-d',time())." 23:59:59";
		}
		$query = $this->change_logic->txListNo($limit,$username,$time1,$time2);
		$money = 0;
		foreach ($query as $v){
			$money += $v['txje'];
			$v['ddhm'] = 'T'.$v['txtime'].$v['uuid'];
		}
		$this->assign('username',$username);
		
		$this->assign('money',$money);
		$this->assign('query',$query);
		return $this->fetch();
	}
	/**
	 * 彩金发放列表
	 * @return Ambigous <\think\mixed, string>
	 */
	public function cj_list(){
		$limit=input('get.limit')?input('get.limit'):20;
		$this->assign("limit",$limit);
		$username=input("post.us_sql")?input('post.us_sql'):input('get.username');
		$time1=input('post.sDate1')?input('post.sDate1'):input('get.time1');
		$time2=input('post.sDate2')?input('post.sDate2'):input('get.time2');
		$this->assign('time1',$time1);
		$this->assign('time2',$time2);
		if($time1 || $time2){
			$time1=$time1?$time1." 00:00:00":date('Y-m-d',time())." 00:00:00";
			$time2=$time2?$time2." 23:59:59":date('Y-m-d',time())." 23:59:59";
		}
		$query = $this->change_logic->caiJinList($limit,$username,$time1,$time2);
		$money = 0;
		foreach ($query as $v){$money += $v['cjje'];}
		$this->assign('username',$username);
		
		$this->assign('money',$money);
		$this->assign('query',$query);
		return $this->fetch();
	}
	
	
	/**
	 * 手工扣款列表
	 * @return Ambigous <\think\mixed, string>
	 */
	public function cz_sgkk_list(){
		$limit=input('get.limit')?input('get.limit'):20;
		$this->assign("limit",$limit);
		$username=input("post.us_sql")?input('post.us_sql'):input('get.username');
		$time1=input('post.sDate1')?input('post.sDate1'):input('get.time1');
		$time2=input('post.sDate2')?input('post.sDate2'):input('get.time2');
		$this->assign('time1',$time1);
		$this->assign('time2',$time2);
		if($time1 || $time2){
			$time1=$time1?$time1." 00:00:00":date('Y-m-d',time())." 00:00:00";
			$time2=$time2?$time2." 23:59:59":date('Y-m-d',time())." 23:59:59";
		}
		$list = $this->change_logic->czSgkkList($limit,$username,$time1,$time2);
		$money=0;
		foreach ($list as $v){$money += $v['kkje'];}
		$this->assign('money',$money);
		$this->assign('username',$username);
		
		$this->assign('query',$list);
		return $this->fetch();
	}
	/**
	 * 手工充值列表
	 * @return Ambigous <\think\mixed, string>
	 */
	public function cz_sgcz_list(){
		$limit=input('get.limit')?input('get.limit'):20;
		$this->assign("limit",$limit);
		$username=input("us_sql");
		
		$time1=input('post.sDate1')?input('post.sDate1'):input('get.time1');
		$time2=input('post.sDate2')?input('post.sDate2'):input('get.time2');
		$this->assign('time1',$time1);
		$this->assign('time2',$time2);
		if($time1 || $time2){
			$time1=$time1?$time1." 00:00:00":date('Y-m-d',time())." 00:00:00";
			$time2=$time2?$time2." 23:59:59":date('Y-m-d',time())." 23:59:59";
		}
		$list = $this->change_logic->czSgczList($limit,$username,$time1,$time2);

		$money=0;
		foreach ($list as $v){$money += $v['je'];}
		$this->assign('username',$username);
		
		$this->assign('money',$money);
		$this->assign('query',$list);
		return $this->fetch();
	}
	/**
	 * 手工充值
	 * @return Ambigous <\think\mixed, string>
	 */
	public function cz_sgcz(){
	    
	    $user = input('get.user')?input('get.user'):'';
	    $this->assign('user',$user);
		$this->change_logic->ManualRecharge();
		return $this->fetch();
	}
	/**
	 * 手工扣款
	 * @return Ambigous <\think\mixed, string>
	 */
	public function cz_sgkk(){
	    $user = input('get.user')?input('get.user'):'';
	    $this->assign('user',$user);
		$this->change_logic->ManualDeduction();
		return $this->fetch();
	}
	/**
	 * 彩金发放
	 * @return Ambigous <\think\mixed, string>
	 */
	public function cz_sgcj()
	{
		$this->change_logic->PaymentOfPrizeMoney();
		return $this->fetch();
	}
	/**
	 * 内部账号充值
	 * @return Ambigous <\think\mixed, string>
	 */
	public function cz_nbcz()
	{
		$limit=input('get.limit')?input('get.limit'):20;
		$this->assign("limit",$limit);
		$username=input("post.us_sql")?input('post.us_sql'):input('get.username');
		$time1=input('post.sDate1')?input('post.sDate1'):input('get.time1');
		$time2=input('post.sDate2')?input('post.sDate2'):input('get.time2');
		$this->assign('time1',$time1);
		$this->assign('time2',$time2);
		if($time1 || $time2){
			$time1=$time1?$time1." 00:00:00":date('Y-m-d',time())." 00:00:00";
			$time2=$time2?$time2." 23:59:59":date('Y-m-d',time())." 23:59:59";
		}
		$this->assign('username',$username);
		
		$this->change_logic->InternalRecharge();
		return $this->fetch();
	}
	/**
	 * 内部充值列表
	 */
	public function cz_nbcz_list()
	{
		$limit=input('get.limit')?input('get.limit'):20;
		$this->assign("limit",$limit);
		$username=input("post.us_sql")?input('post.us_sql'):input('get.username');
		$time1=input('post.sDate1')?input('post.sDate1'):input('get.time1');
		$time2=input('post.sDate2')?input('post.sDate2'):input('get.time2');
		$this->assign('time1',$time1);
		$this->assign('time2',$time2);
		if($time1 || $time2){
			$time1=$time1?$time1." 00:00:00":date('Y-m-d',time())." 00:00:00";
			$time2=$time2?$time2." 23:59:59":date('Y-m-d',time())." 23:59:59";
		}
		$this->assign('username',$username);
		
		$list = $this->change_logic->InternalList($limit,$username,$time1,$time2);
		$this->assign('query',$list);
		return $this->fetch();
	}
	
	public function cz_nbtx_list()
	{
		$list = $this->change_logic->TiXianNeiBuList(16);
		$this->assign('query',$list);
		return $this->fetch();
	}
	/**
	 * 财务汇总
	 */
	public function financialSummary(){
		$time=timeSelection(input('data'));

		if(input('sDate1') && input('sDate2')){
			$start = strtotime(input('sDate1').' 00:00:00');	
			$end = 	strtotime(input('sDate2').' 23:59:59');
			$time = [$start,$end];
		}
		$user = Db::name('username')->where('nbzh',1)->column('username');
		$users = implode(',', $user);
		//所有类型优惠统计
		$xszf = Db::name('chongzhi_list')->field('SUM(je) as total_je,COUNT(je) as total_count, COUNT(DISTINCT username) as total_user')->where('username','not in',$users)->where(['zt'=>1,'czclass'=>2])->whereTime('cztime','BETWEEN',$time)->find();
		$rgcr = Db::name('chongzhi_list')->field('SUM(je) as total_je,COUNT(je) as total_count, COUNT(DISTINCT username) as total_user')->where('username','not in',$users)->where(['zt'=>1,'czclass'=>3])->whereTime('cztime','BETWEEN',$time)->find();
		$xxcr = Db::name('chongzhi_list')->field('SUM(je) as total_je,COUNT(je) as total_count, COUNT(DISTINCT username) as total_user')->where('username','not in',$users)->where(['zt'=>1,'czclass'=>1])->whereTime('cztime','BETWEEN',$time)->find();
		$txjl = Db::name('tixian_list')->field('SUM(txje) as total_je,COUNT(txje) as total_count, COUNT(DISTINCT username) as total_user')->where('username','not in',$users)->where('zt',1)->whereTime('txtime','BETWEEN',$time)->find();
		$cj_all = Db::name('caijin_list')->field('SUM(cjje) as total_je,COUNT(cjje) as total_count, COUNT(DISTINCT username) as total_user')->where('username','not in',$users)->whereTime('fstime','BETWEEN',$time)->find();
		$ms_all = Db::name('chongzhi_list')->field('SUM(hy_msje) as total_je,COUNT(hy_msje) as total_count, COUNT(DISTINCT username) as total_user')->where('username','not in',$users)->where('zt',1)->whereTime('cztime','BETWEEN',$time)->find();
		$level_all = Db::name('chongzhi_list')->field('SUM(hy_zsje) as total_je,COUNT(hy_zsje) as total_count, COUNT(DISTINCT username) as total_user')->where('username','not in',$users)->where('zt',1)->whereTime('cztime','BETWEEN',$time)->find();
		$fs_all = Db::name('fs_list')->field('SUM(fsje) as total_je,COUNT(fsje) as total_count, COUNT(DISTINCT username) as total_user')->where('username','not in',$users)->whereTime('distribute_time','BETWEEN',$time)->find();
		$dlyj = $yjje = Db::name('agent_balance')->field('SUM(balance) as balance,COUNT(*) as uid, COUNT(DISTINCT agent) as agent')->where('agent','not in',$users)->whereTime('add_time','BETWEEN',$time)->find();
		$reg_all = Db::name('bank_caijin')->field('SUM(bankje) as total_je,COUNT(bankje) as total_count, COUNT(DISTINCT username) as total_user')->where('username','not in',$users)->whereTime('time','BETWEEN',$time)->find();

		$this->assign('xszf',$xszf);
		$this->assign('rgcr',$rgcr);
		$this->assign('xxcr',$xxcr);
		$this->assign('txjl',$txjl);
		$this->assign('cj_all',$cj_all);
		$this->assign('ms_all',$ms_all);
		$this->assign('level_all',$level_all);
		$this->assign('reg_all',$reg_all);
		$this->assign('fs_all',$fs_all);
		$this->assign('dlyj',$dlyj);
		$this->assign('start',$time[0]);
		$this->assign('end',$time[1]);

//        $profit_and_loss = $xszf['total_je'] - $txjl['total_je'];

		$profit_and_loss = 0;
		if( isset($xszf['total_je']) ) $profit_and_loss += floatval($xszf['total_je']);
		if( isset($rgcr['total_je']) ) $profit_and_loss += floatval($rgcr['total_je']);
		if( isset($xxcr['total_je']) ) $profit_and_loss += floatval($xxcr['total_je']);
		if( isset($txjl['total_je']) ) $profit_and_loss -= floatval($txjl['total_je']);
		$this->assign('profit_and_loss',sprintf('%.2f',$profit_and_loss));
		return $this->fetch('cw_hz');
	}
	
	/**
	 * 线下支付列表
	 */
	public function xszfList(){
		$start = input('start');
		$end = input('end');
		$id = input('id');
		$time = [intval($start),intval($end)];
		$label = Db::name('pay_xxlist')->where(['id'=>$id])->value('label');
		$user = Db::name('username')->where('nbzh',1)->column('username');
		$users = implode(',', $user);
	    $query = Db::name('chongzhi_list')->field('id,username,je,hy_msje,hy_zsje,cztime')->where(['binary ddbh'=>['like',$label.'%']])->where('username','not in',$users)->where(array('zt'=>1,'czclass'=>2))->whereTime('cztime','BETWEEN',$time)->order('cztime','DESC')->paginate(10,false,['query'=>request()->param()]);
		$this->assign('query',$query);
	    return $this->fetch('xszf_list');
	}
	public function unlineList(){

		$start = input('start');
		$end = input('end');
		$time = [intval($start),intval($end)];
		$user = Db::name('username')->where('nbzh',1)->column('username');
		$users = implode(',', $user);

		$list = Db::name('chongzhi_list')->field('ddbh,je')->where('username','not in',$users)->where(array('zt'=>1,'czclass'=>2))->whereTime('cztime','BETWEEN',$time)->select();
		$list_tmp = [];
		foreach( $list as $v ){
			$head = substr(trim($v['ddbh']),0,1);
			if( isset($list_tmp[$head]) ){
				$list_tmp[$head]['money'] += floatval($v['je']);
				$list_tmp[$head]['num']++;
			}else{
				$list_tmp[$head]['money'] = floatval($v['je']);
				$list_tmp[$head]['num'] = 1;
			}
		}
		$pay_off = Db::name('pay_xxlist')->field('id,xx_name,xx_weixin,xx_zhifubao,xx_wangyin,xx_qq,xx_bankren,label')->select();
		$pay_off = array_map( function($v) use($list_tmp){
			$v['pay_name'] = $v['xx_name'].'_';
			if( 1==$v['xx_weixin'] ){
				$v['pay_name'] .= '微信扫码';
			}elseif( 1==$v['xx_zhifubao'] ){
				$v['pay_name'] .= '支付宝扫码';
			}elseif( 1==$v['xx_wangyin'] ){
				$v['pay_name'] .= '网银转账';
			}elseif( 1==$v['xx_qq'] ){
				$v['pay_name'] .= 'QQ扫码';
			}else{
				$v['pay_name'] .= '未知';
			}
			$v['money'] = isset($list_tmp[$v['label']]) ? $list_tmp[$v['label']]['money'] : 0;
			$v['num'] = isset($list_tmp[$v['label']]) ? $list_tmp[$v['label']]['num'] : 0;
			unset($v['xx_name'],$v['xx_weixin'],$v['xx_zhifubao'],$v['xx_wangyin'],$v['xx_qq']);
			return $v;
		},$pay_off );

		$this->assign('start',$start);
		$this->assign('end',$end);
		$this->assign('query',$pay_off);
		return $this->fetch('unline_list');
	}
	
	/**
	 * 人工存入列表
	 */
	public function rgcrList(){
		$start = input('start');
		$end = input('end');
		$time = [intval($start),intval($end)];
		$user = Db::name('username')->where('nbzh',1)->column('username');
		$users = implode(',', $user);
	    $query = Db::name('chongzhi_list')->field('id,username,je,beizhu,cztime,openren,xxbank')->where('username','not in',$users)->where(['zt'=>1,'czclass'=>3])->whereTime('cztime','BETWEEN',$time)->order('cztime','DESC')->paginate(10,false,['query'=>request()->param()]);
	    $this->assign('query',$query);
	    return $this->fetch('rgcr_list');
	}
	
	/**
	 * 会员出款列表
	 */
	public function txjlList(){
		$start = input('start');
		$end = input('end');
		$time = [intval($start),intval($end)];
		$user = Db::name('username')->where('nbzh',1)->column('username');
		$users = implode(',', $user);
	    $query = Db::name('tixian_list')->field('id,username,txje,txtime,shtime,shadmin')->where('username','not in',$users)->where('zt',1)->whereTime('txtime','BETWEEN',$time)->order('txtime','DESC')->paginate(10,false,['query'=>request()->param()]);
	    $this->assign('query',$query);
	    return $this->fetch('txjl_list');
	}

	/**
	 * 满送优惠列表
	 */
	public function msFavList(){
		$start = input('start');
		$end = input('end');
		$time = [intval($start),intval($end)];
		$user = Db::name('username')->where('nbzh',1)->column('username');
		$users = implode(',', $user);
	    $query = Db::name('chongzhi_list')->field('id,username,je,hy_msje,hy_msbs,cztime')->where('username','not in',$users)->whereTime('cztime','BETWEEN',$time)->where('zt',1)->order('cztime','DESC')->paginate(10,false,['query'=>request()->param()]);
		$this->assign('query',$query);
	    return $this->fetch('ms_fav_list');
	}
	
	/**
	 *  等级优惠列表
	 */
	public function levelFavList(){
		$start = input('start');
		$end = input('end');
		$time = [intval($start),intval($end)];
		$user = Db::name('username')->where('nbzh',1)->column('username');
		$users = implode(',', $user);
	    $query = Db::name('chongzhi_list')->alias('cl')
	                    ->join('__US_CLASS__ uc','uc.id=cl.us_class')
                	    ->field('cl.id,cl.username,uc.class_name,cl.je,cl.hy_zsje,cl.hy_zsbs,cl.cztime')
                	    ->where('cl.zt',1)
                	    ->whereTime('cl.cztime','BETWEEN',$time)
                	    ->order('cl.cztime','DESC')
                	    ->where('cl.username','not in',$users)
                	    ->paginate(10,false,['query'=>request()->param()]);
	    $this->assign('query',$query);
	    return $this->fetch('level_fav_list');
	}
	
	/**
	 * 注册优惠列表
	 */
	public function regFavList(){
		$start = input('start');
		$end = input('end');
		$time = [intval($start),intval($end)];
		$user = Db::name('username')->where('nbzh',1)->column('username');
		$users = implode(',', $user);
	   // $query = Db::name('username')->field('id,username,je,reg_je,dml,reg_time,bk_time')->where('nbzh','<>',1)->where('swzh','<>',1)->whereTime('bk_time','BETWEEN',$time)->order('reg_time','DESC')->paginate(10,false,['query'=>request()->param()]);
	    $query = Db::name('bank_caijin')
	    			->alias('b')
	    			->join('__USERNAME__ u','u.username=b.username')
	    			->field('b.*,u.je,u.dml')
	    			->whereTime('b.time','BETWEEN',$time)
	    			->where('u.username','not in',$users)
	    			->order('b.time','DESC')->paginate(10,false,['query'=>request()->param()]);
	    $this->assign('query',$query);
	    return $this->fetch('reg_fav_list');
	}
	
	/**
	 * 彩金优惠列表
	 */
	public function cjFavList(){
		$start = input('start');
		$end = input('end');
		$time = [intval($start),intval($end)];
		$user = Db::name('username')->where('nbzh',1)->column('username');
		$users = implode(',', $user);
	    $query = Db::name('caijin_list')->alias('cl')
                	    ->join('__US_CLASS__ uc','uc.id=cl.user_class')
                	    ->field('cl.id,cl.username,uc.class_name,cl.cjje,cl.beizhu,cl.fstime')
                	    ->whereTime('cl.fstime','BETWEEN',$time)
                	    ->order('cl.fstime','DESC')
                	    ->where('cl.username','not in',$users)
                	    ->paginate(10,false,['query'=>request()->param()]);
	    $this->assign('query',$query);
	    return $this->fetch('cj_fav_list');
	}
	
	/**
	 * 返水优惠列表
	 */
	public function fsFavList(){
		$start = input('start');
		$end = input('end');
		$time = [intval($start),intval($end)];
		$user = Db::name('username')->where('nbzh',1)->column('username');
		$users = implode(',', $user);
	    $query = Db::name('fs_list')->alias('fl')
	                    ->join('__US_CLASS__ uc','uc.id=fl.us_class')
                	    ->field('fl.id,fl.username,uc.class_name,fl.tzje,fl.zjje,fl.fsje,fl.fsbl,fl.distribute_time')
                	    ->whereTime('distribute_time','BETWEEN',$time)
                	    ->where('fl.username','not in',$users)
                	    ->order('distribute_time','DESC')
                	    ->paginate(10,false,['query'=>request()->param()]);
	    $this->assign('query',$query);
	    return $this->fetch('fs_fav_list');
	}
	/**
	 * 线上支付列表
	 * 
	 */
	public function xxzfList()
	{
		$start = input('start');
		$end = input('end');
		$time = [intval($start),intval($end)];

		$id = input('id',0);
		$type = input('type',0);
		$user = Db::name('username')->where('nbzh',1)->column('username');
		$users = implode(',', $user);

		$pay_name = '';
		if( 1==$type ){
			$pay_name = Db::name('pay_xianshang')->where(['id'=>$id])->value('label');
		}elseif( 2==$type ){
			$pay_name = Db::name('code_account')->where(['id'=>$id])->value('label');
		}
		$query = Db::name('chongzhi_list')->field('id,username,je,beizhu,cztime,openren,xxbank')->where(['binary ddbh'=>['like',$pay_name.'%']])->where('username','not in',$users)->where(['zt'=>1,'czclass'=>1])->whereTime('cztime','BETWEEN',$time)->order('cztime','DESC')->paginate(10,false,['query'=>request()->param()]);

		$this->assign('query',$query);
		return $this->fetch('rgcr_list');
	}
	function onlineList(){

		$start = input('start');
		$end = input('end');
		$time = [intval($start),intval($end)];
		$user = Db::name('username')->where('nbzh',1)->column('username');
		$users = implode(',', $user);
		$list = Db::name('chongzhi_list')->field('id,ddbh,je')->where('username','not in',$users)->where(['zt'=>1,'czclass'=>1])->whereTime('cztime','BETWEEN',$time)->select();
		$between = [];
		foreach( $list as $v ){
			$label = substr($v['ddbh'],0,1);
			if(isset($between[$label]['money'])){
				$between[$label]['money'] += $v['je'];
				$between[$label]['num'] ++;
			}else{
				$between[$label]['money'] = $v['je'];
				$between[$label]['num'] = 1;
			}
		}
		$pay_three = Db::name('pay_xianshang')->field('id,pay_name,1 as type,label')->where(['is_code'=>0])->select();
		$pay_four = Db::name('code_account')->alias('a')->join('__PAY_XIANSHANG__ b','a.type=b.label','LEFT')->field("a.id,a.name,b.pay_name,2 as type,a.label")->where(['b.is_code'=>1])->select();

		$pay = array_merge($pay_three,$pay_four);

		$pay = array_map( function($v) use($between){
			if( 1==$v['type'] ){
				$v['pay_name'] = $v['pay_name'].'_第三方支付';
			}else{
				$v['pay_name'] = $v['name'].'_'.$v['pay_name'].'_第四方支付';
			}
			$v['money'] = isset($between[$v['label']]['money']) ? $between[$v['label']]['money'] : 0;
			$v['num'] = isset($between[$v['label']]['num']) ? $between[$v['label']]['num'] : 0;
			return $v;
		},$pay );
		$this->assign('start',$start);
		$this->assign('end',$end);
		$this->assign('query',$pay);
		return $this->fetch('on_line');
	}
	/**
	 * 代理返佣
	 */
	public function dlfyDetail(){
		$start = input('start');
		$end = input('end');
		$time = [intval($start),intval($end)];
		$user = Db::name('username')->where('nbzh',1)->column('username');
		$users = implode(',', $user);
		$balance = Db::name('agent_balance')->group("agent,FROM_UNIXTIME(add_time,'%Y-%m-%d')")->field("FROM_UNIXTIME(add_time,'%Y-%m-%d') as in_time,sum(balance) as total,agent")->whereTime('add_time','BETWEEN',$time)->where('agent','not in',$users)->order("FROM_UNIXTIME(add_time,'%Y-%m-%d') DESC")->paginate(10,false,['query'=>request()->param()]);
		$this->assign('query',$balance);
		return $this->fetch('dlfy_detail');
	}
	public function dlfyList(){
		$start = input('start');
		$end = input('end');
		$time = [intval($start),intval($end)];
		$user = Db::name('username')->where('nbzh',1)->column('username');
		$users = implode(',', $user);
		$query = Db::name('agent_balance')
					->alias('a')
					->join('__CAIPAI_LIST__ c','c.id=a.czid')
					->join('__USERNAME__ u','u.username=a.agent')
					->field('a.*,c.cp_name_old,c.cp_name_new')
					->whereTime('a.add_time','BETWEEN',$time)
					->where('a.agent','not in',$users)
					->order('a.id','DESC')->paginate(10,false,['query'=>request()->param()]);
		$this->assign('query',$query);
		return $this->fetch('dlfy_list');
	}
	public function shenhe(){
		$us = input('post.user/a');
		if(!empty($us)){
			foreach ($us as $key => $val) {
				# code...
				$banlance = Db::name('agent_balance')->where('agent',$val)->field("id,balance,agent,status")->where('status',0)->order("FROM_UNIXTIME(add_time,'%Y-%m-%d') DESC")->select();
				$je = 0;
				foreach ($banlance as $it){
					$je += $it['balance'];
					Db::name('agent_balance')->where('id',$it['id'])->update(array('status'=>1));
				}	
				$name = Db::name('username')->where('username',$val)->field('je')->find();	
				$data = array('balance'=>sprintf("%.2f",$je),'agent'=>$val,'fyqje'=>sprintf("%.2f",$name['je']),'add_time'=>time());
				Db::name('day_agent')->data($data)->insert();
				Db::name('username')->where('username',$val)->update(['je'=>['exp',"je+{$je}"]]);
			}
			js_tishi('成功', 'agentList?ticker=1');
		}else{
			alert_back('必须选择返佣的代理');
		}
		
	}
	
	public function setopen(){
		$open = input('open');
		if(Db::name('fy_open')->where('id',1)->update(array('open'=>$open))){
			js_tishi('成功','agentList');
		}else{
			alert_back('失败');
		}
	}
	
	/**
	 *
	 * @return Ambigous <\think\mixed, string>
	 */
	public function agentList(){
		$u = input('ss_sql')?input('ss_sql') : '';
		$ticker = input('ticker','');
		$start = strtotime(input('sDate1').' 00:00:00');
		$end = strtotime(input('sDate2') .'23:59:59');
		$limit = input('limit') ? input('limit') : '20';
		$time = [intval($start),intval($end)];
		$user = Db::name('username')->where('nbzh',1)->column('username');
		$users = implode(',', $user);
		$where=array();
		$get = request()->param();
		array_shift($get);
		if(!empty($u)){
			if($ticker==1){
				$balance = Db::name('agent_balance')->where('agent','like','%'.$u.'%')->group("agent,FROM_UNIXTIME(add_time,'%Y-%m-%d')")->field("FROM_UNIXTIME(add_time,'%Y-%m-%d') as in_time,sum(balance) as total,agent,status,fystatus")->where('status',0)->where('balance','>',0)->where('agent','not in',$users)->order("FROM_UNIXTIME(add_time,'%Y-%m-%d') DESC,agent desc")->paginate($limit,false,['query'=>$get]);
			}else{
				$balance = Db::name('agent_balance')->where('agent','like','%'.$u.'%')->group("agent,FROM_UNIXTIME(add_time,'%Y-%m-%d')")->field("FROM_UNIXTIME(add_time,'%Y-%m-%d') as in_time,sum(balance) as total,agent,status,fystatus")->where('balance','>',0)->whereTime('add_time','BETWEEN',$time)->where('agent','not in',$users)->order("FROM_UNIXTIME(add_time,'%Y-%m-%d') DESC,agent desc")->paginate($limit,false,['query'=>$get]);
			}
		}else{
			if($ticker==1){
				$balance = Db::name('agent_balance')->group("agent,FROM_UNIXTIME(add_time,'%Y-%m-%d')")->field("FROM_UNIXTIME(add_time,'%Y-%m-%d') as in_time,sum(balance) as total,agent,status,fystatus")->where('balance','>',0)->where('status',0)->where('agent','not in',$users)->order("FROM_UNIXTIME(add_time,'%Y-%m-%d') DESC,agent desc")->paginate($limit,false,['query'=>$get]);
			}else{
				$balance = Db::name('agent_balance')->group("agent,FROM_UNIXTIME(add_time,'%Y-%m-%d')")->field("FROM_UNIXTIME(add_time,'%Y-%m-%d') as in_time,sum(balance) as total,agent,status,fystatus")->where('balance','>',0)->whereTime('add_time','BETWEEN',$time)->where('agent','not in',$users)->order("FROM_UNIXTIME(add_time,'%Y-%m-%d') DESC,agent desc")->paginate($limit,false,['query'=>$get]);
			}
		}
		//p( $balance);exit;
		$open = Db::name('fy_open')->where('id',1)->Value('open');
		$this->assign('open',$open);
		$this->assign('limit',$limit);
		$this->assign('ticker',$ticker);
		$this->assign('time',$time);
		$this->assign('user',$u);
		$this->assign('query',$balance);
		return $this->fetch('agent_list');
	}
	public function agentSave(){
		$u = input('ss_sql')?input('ss_sql') : '';
		$limit = input('limit') ? input('limit') : '20';
		$get = request()->param();
		array_shift($get);
		if(empty($u)){
			$us = Db::name('username')->field('id,username,fystatus')->
				where(['swzh'=>0,'open'=>0])->paginate($limit,false,['query'=>$get]);
		}else{
			$us = Db::name('username')->field('id,username,fystatus')->where('username',$u)
			       ->where(['swzh'=>0,'open'=>0])->paginate($limit,false,['query'=>$get]);
		}

		$open = Db::name('fy_open')->where('id',1)->Value('open');
		$this->assign('open',$open);
		$this->assign('us',$us);
		$this->assign('user',$u);
		$this->assign('limit',$limit);
		
		return $this->fetch('agent_save');
	}
	public function fysave(){
		$uid = input('post.uid/a');
		$fystatu=input('post.fyfangshi_post');
		
		if(!empty($uid)){
			foreach($uid as $id){
				$id = explode('-',$id);
				if($id[1]!=$fystatu){
					$u=$fystatu;
					Db::name('username')->where('id',$id[0])->update(array('fystatus'=>$u));
				}
				
			}
			js_tishi('设置成功','agentSave');
		}else{
			alert_back('请选择设置的用户');
		}
	}
	public function fanyong(){
		$user = input('uid');
		$banlance = Db::name('agent_balance')->where('agent',$user)->field("id,balance,agent,status")->where('status',0)->order("FROM_UNIXTIME(add_time,'%Y-%m-%d') DESC")->select();
		$je = 0;
		foreach ($banlance as $it){
			$je += $it['balance'];
			
			Db::name('agent_balance')->where('id',$it['id'])->update(array('status'=>1,'fystatus'=>1));
		}	
		$name = Db::name('username')->where('username',$user)->field('je')->find();	
		$data = array('balance'=>sprintf("%.2f",$je),'agent'=>$user,'fyqje'=>sprintf("%.2f",$name['je']),'add_time'=>time());
		Db::name('day_agent')->data($data)->insert();
		if(Db::name('username')->where('username',$user)->update(['je'=>['exp',"je+{$je}"]])){
			js_tishi('成功', 'agentList');
		}else{
			alert_back('发放失败');
		}
	}
	/**
	 * 彩金账变
	 */
	public function cjAccount() {
	    $where = array();
	    $us_sql = input('get.us_sql') ? input('get.us_sql') : '';
	    $sDate1 = input('get.sDate1') ? input('get.sDate1') : date('Y-m-d');
	    $sDate2 = input('get.sDate2') ? input('get.sDate2') : date('Y-m-d');
	    //搜索
	    if($us_sql) {
	        $where['username'] = ['LIKE',"%{$us_sql}%"];
	        $this->assign('us_sql',$us_sql);
	    }
	    $start = strtotime($sDate1." 00:00:00");
	    $end = strtotime($sDate2." 23:59:59");
	    $this->assign('start',$sDate1);
	    $this->assign('end',$sDate2);
	    $time = [$start,$end];
	    $user = Db::name('username')->where('nbzh',1)->column('username');
	    $users = implode(',', $user);
	    //$query = Db::name('caijin_list')->field('id,username,cjje,cjhje,fstime')->where('username','not in',$users)->where($where)->order('fstime','DESC')->paginate(10,false,['query'=>request()->param()]);
		$query = Db::field('id,username,cjje as je,0 as hy_zsje,0 as hy_msje,cjhje as czhje,fstime as cztime,0 as type,0 as ext')
					->table('kkty_caijin_list')->where('username','not in',$users)->where($where)->where('fstime','BETWEEN',$time)
					->union(function($query) use($where,$time,$users){
						$query->field('id,username,txje as je,0 as hy_zsje,0 as hy_msje,txhje as czhje,txtime as cztime,1 as type,0 as ext')->table('kkty_tixian_list')->where('username','not in',$users)->where($where)->where('zt',1)->where('txtime','BETWEEN',$time);
					})->union(function($query) use($where,$time,$users){
						$query->field('id,username,je,hy_zsje,hy_msje,czhje,cztime,2 as type,method_money as ext')->table('kkty_chongzhi_list')->where('username','not in',$users)->where($where)->where('zt',1)->where('cztime','BETWEEN',$time);
					})->select();
		if($query){
			$query = arraySequence($query, 'cztime');
		}
	    $this->assign('query',$query);
	    return $this->fetch('cj_account');
	}
	/**
	 * 总账变
	 */
	public function zzAccount(){

		//充值账变
		$where = array();
		$us_sql = input('get.us_sql') ? input('get.us_sql') : '';
		$sDate1 = input('get.sDate1') ? strtotime(input('sDate1').' 00:00:00') : strtotime(date('Y-m-d 00:00:00'));
		$sDate2 = input('get.sDate2') ? strtotime(input('sDate2').' 23:59:59') : strtotime(date('Y-m-d 23:59:59'));
		if($us_sql!='') {
			$where['username'] = ['LIKE',"%{$us_sql}%"];
		}
		$this->assign('us_sql',$us_sql);
		
		$betweentime = [$sDate1,$sDate2];
		$this->assign('start',date('Y-m-d',$sDate1));
		$this->assign('end',date('Y-m-d',$sDate2));
		$user = Db::name('username')->where('nbzh',1)->column('username');
		$users = implode(',', $user);

		$chongzhi = Db::name('chongzhi_list')
					->field('id,username,0 as czid,0 as type,je,hy_zsje,hy_msje,czhje,cztime,0 as fdje,0 as zjje,method_money as ext,czqje')
					->where($where)->where(['zt'=>1])->whereTime('cztime','BETWEEN',$betweentime)->where('username','not in',$users)
					->fetchSql(true)->select();
		$tixian = Db::name('tixian_list')
					->field('id,username,0 as czid,1 as type,txje as je,0 as hy_zsje,0 as hy_msje, txhje as czhje,txtime as cztime,0 as fdje,0 as zjje,0 as ext,0 as czqje')
					->where('username','not in',$users)->where($where)->where(['zt'=>1])->whereTime('txtime','BETWEEN',$betweentime)
					->fetchSql(true)->select();
		$tixian_no = Db::name('tixian_list_no')
					->field('id,username,0 as czid,1 as type,txje as je,0 as hy_zsje,0 as hy_msje, txhje as czhje,txtime as cztime,0 as fdje,0 as zjje,0 as ext,0 as czqje')
					->where('username','not in',$users)->where($where)->where(['zt'=>2])->whereTime('txtime','BETWEEN',$betweentime) 
					->fetchSql(true)->select();
		$tixian_no_two = Db::name('tixian_list_no')
					->field('id,username,0 as czid,11 as type,txje as je,0 as hy_zsje,0 as hy_msje, thqje as czhje,shtime as cztime,0 as fdje,0 as zjje,0 as ext,0 as czqje')
					->where('username','not in',$users)->where($where)->where(['zt'=>2])->whereTime('shtime','BETWEEN',$betweentime)
					->fetchSql(true)->select();
		$caijin = Db::name('caijin_list')
					->field('id,username,0 as czid, 2 as type,cjje as je,0 as hy_zsje,0 as hy_msje, cjhje as czhje,fstime as cztime,0 as fdje,0 as zjje,beizhu as ext,0 as czqje')
					->where('username','not in',$users)->where($where)->whereTime('fstime','BETWEEN',$betweentime)
					->fetchSql(true)->select();
		$touzhu_no = Db::name('touzhu_list_no')
					->field('id,username,czid,3 as type,tzje as je,0 as hy_zsje,0 as hy_msje, tzhje as czhje,tz_time as cztime,fdje,zjje,0 as ext,0 as czqje')
					->where('username','not in',$users)->where($where)->whereTime('tz_time','BETWEEN',$betweentime)
					->fetchSql(true)->select();
		$touzhu = Db::name('touzhu_list_ok')
					->field('id,username,czid,3 as type,tzje as je,0 as hy_zsje,0 as hy_msje, tzhje as czhje,tz_time as cztime,fdje,zjje,0 as ext,0 as czqje')
					->where('username','not in',$users)->where($where)->whereTime('tz_time','BETWEEN',$betweentime)
					->fetchSql(true)->select();
		$touzhu_pc = Db::name('touzhu_list_pc')
					->field('id,username,czid,5 as type,zjje as je,0 as hy_zsje,0 as hy_msje, tzhje as czhje,UNIX_TIMESTAMP(rktime) as cztime,fdje,zjje,0 as ext,0 as czqje')
					->where('username','not in',$users)->where($where)->whereTime('tz_time','BETWEEN',$betweentime)
					->fetchSql(true)->select();
		$fanyong = Db::name('day_agent')
					->field('id,agent as username,0 as czid,6 as type,balance as je,0 as hy_zsje,0 as hy_msje, fyqje as czhje,add_time as cztime,0 as fdje,0 as zjje,0 as ext,0 as czqje')
					->where('agent','not in',$users)->where("agent like '%{$us_sql}%'")->whereTime('add_time','BETWEEN',$betweentime)
					->fetchSql(true)->select();
		$sgkk = Db::name('sgkk_list')
				->field('id,username,0 as czid,7 as type,kkje as je,0 as hy_zsje,0 as hy_msje, kkhje as czhje,kktime as cztime,0 as fdje,0 as zjje,0 as ext,0 as czqje')
				->where('username','not in',$users)->where($where)->whereTime('kktime','BETWEEN',$betweentime)
				->fetchSql(true)->select();
		$chedan = Db::name('touzhu_list_cd')
					->field('id,username,czid,8 as type,tzje as je,0 as hy_zsje,0 as hy_msje, tzhje as czhje,tz_time as cztime,fdje,zjje,0 as ext,0 as czqje')
					->where('username','not in',$users)->where($where)->whereTime('tz_time','BETWEEN',$betweentime)
					->fetchSql(true)->select();
		$chedan2 = Db::name('touzhu_list_cd')
					->field('id,username,czid,9 as type,tzje as je,0 as hy_zsje,0 as hy_msje, cdqje as czhje,rktime as cztime,fdje,zjje,0 as ext,0 as czqje')
					->where('username','not in',$users)->where($where)->whereTime('rktime','BETWEEN',$betweentime)
					->fetchSql(true)->select();
		$zhuihao = Db::name('touzhu_list_zh')
					->field('id,username,czid,3 as type,tzje as je,0 as hy_zsje,0 as hy_msje, tzhje as czhje,tz_time as cztime,fdje,zjje,0 as ext,0 as czqje')
					->where('username','not in',$users)->where($where)->whereTime('tz_time','BETWEEN',$betweentime)
					->fetchSql(true)->select();
		$tuikuan = Db::name('touzhu_list_tk')
					->field('id,username,czid,8 as type,tzje as je,0 as hy_zsje,0 as hy_msje, tzhje as czhje,tz_time as cztime,fdje,zjje,0 as ext,0 as czqje')
					->where('username','not in',$users)->where($where)->whereTime('tz_time','BETWEEN',$betweentime)
					->fetchSql(true)->select();
		$tuikuan2 = Db::name('touzhu_list_tk')
					->field('id,username,czid,10 as type,tzje as je,0 as hy_zsje,0 as hy_msje, tkqje as czhje,tktime as cztime,fdje,zjje,0 as ext,0 as czqje')
					->where('username','not in',$users)->where($where)->whereTime('tktime','BETWEEN',$betweentime)
					->fetchSql(true)->select();
		$hongbao = Db::name('bonus_user')
					->field('id,username,0 as czid,13 as type,je,0 as hy_zsje,0 as hy_msje, 0 as czhje,time as cztime,0 as fdje,0 as zjje,0 as ext,0 as czqje')
					->where('username','not in',$users)->where($where)->whereTime('time','BETWEEN',$betweentime)
					->fetchSql(true)->select();
		$fandian = Db::name('touzhu_list_ok')
					->field('id,username,czid,14 as type,fdje as je,0 as hy_zsje,0 as hy_msje, tzhje as czhje,tz_time as cztime,0 as fdje,zjje,0 as ext,0 as czqje')
					->where('username','not in',$users)->where($where)->where('fdje','>',0)->whereTime('tz_time','BETWEEN',$betweentime)
					->fetchSql(true)->select();
		

		$page = input('page',1);
		$limit = input('limit','15');
		$begin = ($page-1)*$limit;
		$sql = "SELECT * FROM (({$chongzhi}) UNION ({$touzhu}) UNION ({$tixian}) UNION ({$caijin}) UNION ({$tuikuan}) UNION ({$tixian_no}) UNION ({$tixian_no_two}) UNION ({$tuikuan2}) UNION ({$touzhu_no}) UNION ({$zhuihao}) UNION ({$chedan}) UNION ({$fandian}) UNION ({$chedan2}) UNION ({$hongbao}) UNION ({$touzhu_pc}) UNION ({$fanyong}) UNION ({$sgkk})) as log ORDER BY log.cztime DESC,id DESC LIMIT {$begin},{$limit};";
		$total_sql = "SELECT count(*) as num FROM (({$chongzhi}) UNION ({$touzhu}) UNION ({$tixian})  UNION ({$tixian_no}) UNION ({$tixian_no_two}) UNION ({$caijin}) UNION ({$tuikuan}) UNION ({$tuikuan2}) UNION ({$touzhu_no}) UNION ({$zhuihao}) UNION ({$chedan}) UNION ({$fandian}) UNION ({$chedan2}) UNION ({$hongbao}) UNION ({$touzhu_pc}) UNION ({$fanyong}) UNION ({$sgkk})) as log;";
		$query = Db::query($sql);
		$total_query = Db::query($total_sql);
		$total = $total_query[0]['num'];
		$total_page = ceil($total/$limit);
		$cp = Db::name('caipai_list')->column("id,cp_name_old,cp_name_new");
//		foreach ($query as &$item){
//			if($item['czid']!=0){
//				$cp = Db::name('caipai_list')->where('id',$item['czid'])->find();
//				$item['czid'] = $cp['cp_name_new']?$cp['cp_name_new']:$cp['cp_name_old'];
//				if(ctype_digit($item['cztime'])===false){
//					$item['cztime'] = intval(strtotime($item['cztime']));
//				}
//			}
//		}
//		if($query){
//			array_multisort(array_column($query,'cztime'),SORT_DESC,array_column($query,'id'),SORT_DESC,$query);
//		}
		$this->assign('query',$query);
		$this->assign('cp',$cp);
		$this->assign('limit',$limit);
		$this->assign('page',intval($page));
		$this->assign('total',$total);
		$this->assign('total_page',$total_page);
		return $this->fetch('zz_account');
	}

	public function treeDaysList(){
		$seachtime = input('seachtime');
		$sj = input('sj');
		$cope = input('cope')?input('cope'):'';
		if($sj==0){
			$three_region = strtotime($seachtime.' -3 day');
		}
		if($sj==1){
			$three_region = strtotime($seachtime.' -7 day');
		}
		if($sj==2){
			$three_region = strtotime($seachtime.' -30 day');
		}
		if($sj==3){
			$three_region = strtotime($seachtime.' -'.$cope.' day');
		}
		$time = [$three_region,strtotime($seachtime)];
		$tz_query = Db::name('touzhu_list_ok')
                            ->field('username,count(id) as num')
                            ->whereTime('tz_time','BETWEEN',$time)
                            ->group('username')
                            ->buildSql();
        $loss_user = Db::name('username')->alias('u')
                            ->join("{$tz_query} tz",'tz.username=u.username','LEFT')
                            ->field('u.username,u.id,u.reg_time')
                            ->where(['u.swzh'=>0,'u.nbzh'=>0])
                            ->where('tz.num',NULL)
                            ->paginate(10,false,['query'=>request()->param()]);
					
		$this->assign('loss_user',$loss_user);
		return $this->fetch('tree_days_list');
		
	}
	
	
	public function excel(){
		return $this->fetch();
	}
	public function excelupload(){

		$file = request()->file('excel_file'); 
         if($file){
            $file_types = explode(".", $_FILES['excel_file']['name']); // ["name"] => string(25) "excel文件名.xls"
            $file_type = $file_types [count($file_types) - 1];//xls后缀
            /*判别是不是.xls文件，判别是不是excel文件*/
            if (strtolower($file_type) != "xls" && strtolower($file_type) != "xlsx") {
                $this->error('不是Excel文件，重新上传');
                die;
            }
 
            $a = dirname($_SERVER['SCRIPT_FILENAME'])."/";
            $b = dirname(realpath($a))."/";
 
            $path = $b.'public/upload/excel'; 
            $info = $file->move($path); 
            $file_path = $path.'/'.$info->getSaveName(); //上传后的EXCEL路径
            
            $extension=explode('.',$file_path);
            $extension=array_pop($extension);
            switch ($extension) {
                case 'xls':
                    $type = 'Excel5';
                    break;
                case 'xlsx':
                    $type = 'Excel2007';
                    break;
            }
            require'../extend/PHPExcel/PHPExcel.php'; 
            $objReader = \PHPExcel_IOFactory::createReader($type);
 
            $obj_PHPExcel =$objReader->load($file_path, $encode = 'utf-8');  //加载文件内容,编码utf-8
            /*$sheet = $obj_PHPExcel->getSheet(0); //激活当前的表
            $highestRow = $sheet->getHighestRow(); // 取得总行数
            $highestColumn = $sheet->getHighestColumn(); // 取得总列数*/
 
 
            $excel_array=$obj_PHPExcel->getsheet(0)->toArray();   //转换为数组格式
            $array=array_shift($excel_array);  //删除第一个数组(标题);

            
            foreach ($excel_array as $key => $val) {
            	$kktime = time();
            	$adminuser=$_COOKIE['user'];
            	$username=$val[0];
            	$cjje=$val[1];
            	$dml=$val[2];
            	$beizhu=$val[3];

            	$query=Db::name('username')->where('username',$val[0])->find();
            	$dml_new=$dml+$query['dml'];
            	$cjje_new=$cjje+$query['je'];
            	$jine_zong=$query['zcjje']+$cjje;
            	$user_class=$query['us_class'];
            	$savedata = array('je'=>$cjje_new,'dml'=>$dml,'zcjje'=>$jine_zong);
            	$data = array('username'=>$username,'cjje'=>$cjje,'beizhu'=>$beizhu,'czadmin'=>$adminuser,'fstime'=>$kktime,'user_class'=>$user_class,'cjhje'=>$cjje_new);
            	$where = array('username'=>$val[0]);
            	if(!empty($username)){
            		$this->state->InsertRecharge('caijin_list', $data);
		        	$this->state->SaveRecharge('username', $where, $savedata);
		        	$this->state->UserFaSong($val[0], "系统彩金发放，金额：".$val[1]."，原因：".$val[3]);
		        	$this->member_model->caozuorizhi_xieru('通过导入Excel文件给账户:'.$val[0]."发放彩金".$val[1]);
            	}
            	
            }
            
            js_tishi_tan('操作成功！', '/admin/Recharge/cj_list');
		}
			    
    }
	function downExcel(){

		$come = input('wfrom',0);
		$where=array();
		$stua=input('stua',0);
		$username = input('username','');

		$title = '';
		$header = '';
		if($stua==3){
			$title = '线上待支付列表';
			$header="订单号\t玩家\t层级\t付款时间\t提交申请时间\t账号\t存款渠道\t存款金额\t审核状态\t审核人\tIP\r";
			$where['a.zt']=0;$where['a.czclass']='1';
		}elseif($stua==0){
			$title = '线下待审核列表';
			$header="订单号\t玩家\t层级\t付款时间\t提交申请时间\t线下审核时间\t账号\t存款渠道\t存款金额\t审核状态\t审核人\tIP\r";
			$where['a.zt']=0;$where['a.czclass']='2';
		}else if($stua==1){
			$title = '充值审核成功列表';
			$header="订单号\t玩家\t层级\t付款时间\t提交申请时间\t线下审核时间\t账号\t存款渠道\t存款金额\t审核状态\t审核人\tIP\r";
			$where['a.czadmin']=['<>',''];$where['a.zt']='1';
		}else if($stua==2){
			$title = '充值审核未通过列表';
			$header="订单号\t玩家\t层级\t付款时间\t提交申请时间\t账号\t存款渠道\t存款金额\t审核状态\t审核人\tIP\t取消原因\r";
			$where['a.zt']='-1';$where['a.czadmin']=['<>',''];
		}else if($stua==9){
			$title = '无效订单列表';
			$header="订单号\t玩家\t层级\t付款时间\t提交申请时间\t线下审核时间\t账号\t存款渠道\t存款金额\t审核状态\t审核人\tIP\r";
			$where['a.zt']='9';
		}
		if( ''!=$username ){
			$where['a.username'] = $username;
			$title .= '*'.$username;
		}
		$star_time = input('start',0);
		$end_time = input('end',0);

		if( !empty($star_time) || !empty($end_time) ){
			if( !empty($star_time) && !empty($end_time) ){
				$star_time = $star_time." 00:00:00";
				$end_time = $end_time." 23:59:59";
				$where['a.cztime']=['between',strtotime($star_time).','.strtotime($end_time)];
				$title .= '*'.$star_time.'到'.$end_time;
			}elseif(empty($star_time)){
				$end_time = $end_time." 23:59:59";
				$where['a.cztime']= ['<',strtotime($end_time)];
				$title .= '*'.'截止时间:'.$end_time;
			}elseif( empty($end_time) ){
				$star_time = $star_time." 00:00:00";
				$where['a.cztime']= ['>',strtotime($star_time)];
				$title .= '*'.'开始时间:'.$star_time;
			}
		}
		$a = input('title','');
		if( !empty($a) ){
			$title .= '*'.$a;
		}
		if( !empty($come) ){
			$come_attr = explode('_',$come);
			if( 2 == count($come_attr) ){
				if( 1==$come_attr[0] ){
					//第三方
					$pay_name = Db::name('pay_xianshang')->where(['id'=>$come_attr[1]])->value('label');
					$where['binary a.ddbh'] = ['like',$pay_name.'%'];
					$where['a.czclass'] = 1;
				}elseif( 2==$come_attr[0] ){
					//第四方
					$pay_name = Db::name('code_account')->where(['id'=>$come_attr[1]])->value('label');
					$where['binary a.ddbh'] = ['like',$pay_name.'%'];
					$where['a.czclass'] = 1;
					$where['a.czclass'] = 1;
				}elseif( 3==$come_attr[0] ){
					//线下
					$pay_name = Db::name('pay_xxlist')->where(['id'=>$come_attr[1]])->value('label');
					$where['binary a.ddbh'] = ['like',$pay_name.'%'];
					$where['a.czclass'] = 2;
				}
			}
		}
		$order = 'id desc';
		$list = Db::name('chongzhi_list')
				->alias('a')
				->join('__USERNAME__ b','b.username=a.username')
				->join('__US_CLASS__ c','c.id=b.us_class')
				->field('a.id,a.ddbh,a.zt,a.je,a.username,a.czclass,a.xxbank,a.cztime,a.pay_time,a.openren,a.czadmin,a.beizhu,a.czip,a.bankekh,a.liuyan,c.class_name,a.shtime,a.beizhu')
				->where($where)->order($order)->select();
		$ret = [];
		foreach( $list as $v ){
			$pay_time = $v['pay_time'];
			!empty($pay_time) && $pay_time = date('Y-m-d H:i',$pay_time);
			$sh_time = $v['shtime'];
			!empty($sh_time) && $sh_time = date('Y-m-d H:i',$sh_time);
			$status = '未知';
			if( 0==intval($v['zt']) ){
				if( 1==intval($v['czclass']) ){
					$status = '等待支付';
				}else{
					$status = '待审核';
				}
			}else{
				if( 1==intval($v['czclass']) && 1==intval($v['zt']) ){
					$status = '支付成功';
				}elseif( 1!=intval($v['czclass']) && intval($v['zt'])>0 ){
					$status = '审核成功';
				}elseif( 1!=intval($v['czclass']) && intval($v['zt'])<0 ){
					$status = '审核未通过';
				}elseif( 9 == intval($v['zt']) ){
					$status = '无效订单';
				}

			}
			switch(intval($stua)){
				case 0:
					//线下待审核列表
					$ret[] = [$v['ddbh'],$v['username'],$v['class_name'],$pay_time,date('Y-m-d H:i',$v['cztime']),$sh_time,$v['openren'],$v['xxbank'].'--'.$v['liuyan'],$v['je'],$status,$v['czadmin'],$v['czip']];
					break;
				case 1:
					//充值审核成功列表
					$ret[] = [$v['ddbh'],$v['username'],$v['class_name'],$pay_time,date('Y-m-d H:i',$v['cztime']),$sh_time,$v['openren'],$v['xxbank'].'--'.$v['liuyan'],$v['je'],$status,$v['czadmin'],$v['czip']];
					break;
				case 2:
					//充值审核未通过列表
					$ret[] = [$v['ddbh'],$v['username'],$v['class_name'],$pay_time,date('Y-m-d H:i',$v['cztime']),$v['openren'],$v['xxbank'].'--'.$v['liuyan'],$v['je'],$status,$v['czadmin'],$v['czip'],$v['beizhu']];
					break;
				case 3:
					//线上待支付列表
					$ret[] = [$v['ddbh'],$v['username'],$v['class_name'],$pay_time,date('Y-m-d H:i',$v['cztime']),$v['openren'],$v['xxbank'].'--'.$v['liuyan'],$v['je'],$status,$v['czadmin'],$v['czip']];
					break;
				case 9:
					//无效订单列表
					$ret[] = [$v['ddbh'],$v['username'],$v['class_name'],$pay_time,date('Y-m-d H:i',$v['cztime']),$sh_time,$v['openren'],$v['xxbank'].'--'.$v['liuyan'],$v['je'],$status,$v['czadmin'],$v['czip']];
					break;
			}
		}
		self::createtable($header,$ret,$title);
	}
	private static function createtable($header,$list,$filename){
		header("Content-type:application/vnd.ms-excel");
		header("Content-Disposition:filename=".$filename.".xls");
		$strexport = $header;
		foreach ($list as $row){
			$length = count($row);
			for( $i=0;$i<$length;$i++ ){
				$a = $row[$i].'';
				if( $i==$length-1 ){
					$strexport.=$a."\r";
				}else{
					$strexport.=$a."\t";
				}
			}
		}
		$strexport=iconv('UTF-8',"GB2312//IGNORE",$strexport);
		exit($strexport);
	}
}